(ns poi.excel
  (:use util.file
        util.io)
  (:import [org.apache.poi.hssf.usermodel HSSFWorkbook]
           [org.apache.poi.ss.usermodel WorkbookFactory Workbook]
           [java.io FileOutputStream]))

(defn workbook []
  (HSSFWorkbook.))

(defn workbook-open [path]
  (WorkbookFactory/create (java.io.FileInputStream. path)))

(defn save-workbook [wb filename]
  (with-open [out (FileOutputStream. filename)]
    (.write wb out)))

(defn create-sheet [wb name]
  (let [s (.createSheet wb name)]
    s))

(defn get-sheet [wb index]
  {:pre [(or (integer? index) (string? index))]}
  (if (integer? index)
    (.getSheetAt wb index)
    (.getSheet wb index)))

(defn first-sheet [wb]
  (get-sheet wb 0))


(defn auto-cell-value [v]
  (cond
    (integer? v) (double v) ; XXX: how to handle big integer? converting to double may loss
    (string? v) (cond
                  (re-matches #"\d+" v) (Double/parseDouble v)  ; XXX: how to handle big integer? converting to double may loss
                  (re-matches #"\d+\.\d+" v) (Double/parseDouble v)  ; XXX: how to handle big integer? converting to double may loss
                  :else v)
    :else v))

(defn create-row [sheet index]
  (.createRow sheet index))

(defn get-row [sheet index]
  (.getRow sheet index))

(defn get-row! [sheet index]
  (or (get-row sheet index) (create-row sheet index)))

(defn create-cell
  ([row index]
    (.createCell row index))
  ([sheet row col]
    (-> sheet (get-row! row) (create-cell col))))

(defn get-cell
  ([row col]
    (.getCell row col))
  ([sheet row col]
    (when-let [row (get-row sheet row)]
      (get-cell row col))))

(defn get-cell!
  ([row col]
    (or (get-cell row col) (create-cell row col)))
  ([sheet row col]
    (get-cell! (get-row! sheet row) col)))

(defn set-cell-value [cell value]
  (when (some? value)
    (.setCellValue cell value)))

(defn set-numberic-cell-type [cell]
  (.setCellType cell org.apache.poi.ss.usermodel.Cell/CELL_TYPE_NUMERIC))


(defn get-cell-value [cell]
  (condp = (.getCellType cell)
    org.apache.poi.ss.usermodel.Cell/CELL_TYPE_NUMERIC (.getNumericCellValue cell)
    org.apache.poi.ss.usermodel.Cell/CELL_TYPE_STRING (.getStringCellValue cell)
    org.apache.poi.ss.usermodel.Cell/CELL_TYPE_BLANK nil
    org.apache.poi.ss.usermodel.Cell/CELL_TYPE_BOOLEAN (.getBooleanCellValue cell)
    org.apache.poi.ss.usermodel.Cell/CELL_TYPE_ERROR (.getErrorCellValue cell)))

(defn create-data-format [wb]
  (.createDataFormat wb))

(defn get-format [data-format format-string]
  (.getFormat data-format format-string))

(defn create-cell-style [wb]
  (.createCellStyle wb))

(defn set-data-format [style data-format]
  (.setDataFormat style data-format))

(defn set-cell-style [cell style]
  (.setCellStyle cell style))

(defn get-cell-style [cell]
  (.getCellStyle cell))

(defn get-data-format [style]
  (.getDataFormat style))

(defn get-data-format-string [style]
  (.getDataFormatString style))

(defn create-cell-style-by-format [wb format-string]
  (let [style (create-cell-style wb)
        format (create-data-format wb)]
    (set-data-format style (get-format format format-string))
    style))

(defn create-bold-cell-style [wb]
  (let [style (create-cell-style wb)
        font (.createFont wb)]
    (.setBoldweight font org.apache.poi.ss.usermodel.Font/BOLDWEIGHT_BOLD)
    (.setFont style font)
    style))

(defn auto-size-column [sheet i]
  (.autoSizeColumn sheet i))

(defn set-cell-formula [cell formula]
  (.setCellFormula cell formula))

(defn get-cell-formula [cell]
  (.getCellFormula cell))

(defn add-merged-region [sheet first-row last-row first-col last-col]
  (.addMergedRegion sheet (org.apache.poi.ss.util.CellRangeAddress. first-row last-row first-col last-col)))

;;; utils

(defn creation-helper [wb]
  (.getCreationHelper wb))

(defn drawing-patriarch [sheet]
  (.createDrawingPatriarch sheet))

(defn freeze-header [sheet]
  (.createFreezePane sheet 0 1))

;;; hyberlink

(defn link-cells [helper cell1 cell2]
  (let [link (.createHyperlink helper org.apache.poi.common.usermodel.Hyperlink/LINK_DOCUMENT)]
    (.setAddress link (format "'%s'!%s" (.getSheetName (.getSheet cell2)) (.formatAsString (org.apache.poi.ss.util.CellReference. cell2))))
    (.setHyperlink cell1 link)))

;;; image

(defn set-cell-image-from-bytes [wb sheet cell bytes format]
  (let [picture-index (.addPicture wb bytes format)
        anchor (doto (.createClientAnchor (creation-helper wb))
                 (.setCol1 (.getColumnIndex cell))
                 (.setCol2 (inc (.getColumnIndex cell)))
                 (.setRow1 (.getRowNum (.getRow cell)))
                 (.setRow2 (inc (.getRowNum (.getRow cell)))))
        drawing (drawing-patriarch sheet)
        picture (.createPicture drawing anchor picture-index)]
;    (.resize picture)
    picture))

(defn set-cell-image-from-file [wb sheet cell path]
  (let [format (case (.toLowerCase (file-extension path)) ".jpg" Workbook/PICTURE_TYPE_JPEG ".png" Workbook/PICTURE_TYPE_PNG)]
    (set-cell-image-from-bytes wb sheet cell (file->bytes path) format)))


;;; comment

(defn create-comment [factory drawing cell text]
  (let [anchor (.createClientAnchor factory)]
    (.setCol1 anchor (.getColumnIndex cell))
    (.setCol2 anchor (inc (.getColumnIndex cell)))
    (.setRow1 anchor (.getRowNum (.getRow cell)))
    (.setRow2 anchor (+ (.getRowNum (.getRow cell)) 3))
    (let [comment (.createCellComment drawing anchor)
          text (.createRichTextString factory text)]
      (.setString comment text)
      (.setAuthor comment "what?")
      comment)))

(defn set-cell-comment [wb sheet cell comment]
  (let [comment (create-comment (creation-helper wb) (drawing-patriarch sheet) cell comment)]
    (.setCellComment cell comment)))


